package com.tongzhou.tongzhou.link.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.tongzhou.db.DBUtil;
import com.tongzhou.db.exception.DBException;
import com.tongzhou.tongzhou.link.dao.IProductScheduleDAO;
import com.tongzhou.tongzhou.link.model.Degree;
import com.tongzhou.tongzhou.link.model.ProductSchedule;
import com.tongzhou.tongzhou.link.model.SearchProductScheduleCondition;
import com.tongzhou.util.Util;

public class ProductScheduleDAO implements IProductScheduleDAO{

	@Override
	public int addProductSchedule(ProductSchedule productSchedule)
			throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select max(id)+1 from product_schedule");
		Long maxid=Long.valueOf(1);
		try{
			maxid=DBUtil.getCount(sql.toString());
		}catch (DBException e) {
			e.printStackTrace();
		}
		
		if(maxid==null || maxid.equals("")){
			maxid=Long.valueOf(1);
		}
		
		sql.delete(0, sql.length());
		sql.append("insert into product_schedule(id,name,create_time,time) ");
		sql.append("values('"+maxid+"','"+Util.changeToObj(productSchedule.getName())+"',");
		sql.append("now(),");
		sql.append("'"+Util.changeToObj(productSchedule.getTime())+"')");
		int result=DBUtil.update(sql.toString());
		
		for(int i=0;i<4;i++){
			sql.delete(0, sql.length());
			sql.append("select max(id)+1 from link");
			Long maxLinkId=Long.valueOf(1);
			try{
				maxLinkId=DBUtil.getCount(sql.toString());
			}catch (DBException e) {
				e.printStackTrace();
			}
			if(maxLinkId==null || maxLinkId.equals("")){
				maxLinkId=Long.valueOf(1);
			}
			
			sql.delete(0, sql.length());
			sql.append("insert into link(id,link_name,productSchedule_id) ");
			sql.append("values('"+maxLinkId+"',");
			if(i==0){
				sql.append("'一号线',");
			}else if(i==1){
				sql.append("'二号线',");
			}if(i==2){
				sql.append("'三号线',");
			}if(i==3){
				sql.append("'四号线',");
			}
			sql.append("'"+maxid+"')");
			DBUtil.update(sql.toString());
		}
		return result;
	}

	@Override
	public void deleteProductSchedule(int id) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.delete(0, sql.length());
		sql.append("delete from degree where link_id in (select id from link where productSchedule_id='"+id+"')");
		DBUtil.update(sql.toString());
		
		sql.delete(0, sql.length());
		sql.append("delete from link where productSchedule_id='"+id+"'");
		DBUtil.update(sql.toString());
		
		sql.delete(0, sql.length());
		sql.append("delete from product_schedule where id='"+id+"'");
		DBUtil.update(sql.toString());
	}

	@Override
	public ProductSchedule getProductScheduleById(int id) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select id,name,");
		sql.append("date_format(create_time,'%Y-%m-%d') as create_time,");
		sql.append("date_format(time,'%Y-%m-%d') as time ");
		sql.append(" from product_schedule ");
		sql.append(" where id='"+id+"'");
		ProductSchedule productSchedule=DBUtil.query(sql.toString(), new BeanHandler<ProductSchedule>(ProductSchedule.class));
		return productSchedule;
	}

	@Override
	public List<ProductSchedule> listProductSchedule(
			SearchProductScheduleCondition condition) throws DBException {
		List<ProductSchedule> result=null;
		
		//查询条件SQL
		//查询参数
		StringBuffer sqlCondition = new StringBuffer() ;
		List<String> paramList = new ArrayList<String>() ;
		
		//取得满足条件的总记录数和总页数
		StringBuffer sql = new StringBuffer() ;
		sql.append("select count(id) ");
		sql.append(" from product_schedule ");
		sql.append(" where 1=1 ");
		
		//总记录数
		long total = DBUtil.getCount(sql.toString(),paramList.toArray()) ;
		//总页数
		long totalPage = Util.getTotalPage(total, condition.getPageSize());
		
		condition.setTotal(total);
		condition.setTotalPage(totalPage);
		
		//查询某页的列表数据
		sql.delete(0, sql.length());
		sql.append("select  id,name,");
		sql.append("date_format(create_time,'%Y-%m-%d') as create_time,");
		sql.append("date_format(time,'%Y-%m-%d') as time ");
		sql.append(" from product_schedule ");
		sql.append(" where 1=1 ");
		sql.append(" order by "+condition.getOrderBy()+" "+condition.getOrderByType()) ;
		result = DBUtil.query(sql.toString(), new BeanListHandler<ProductSchedule>(ProductSchedule.class), paramList.toArray(), Util.getStartPos(condition.getCurrentPage(), condition.getPageSize()), condition.getPageSize()) ;
		return result;
	}

	@Override
	public void updateProductSchedule(ProductSchedule productSchedule)
			throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("update product_schedule set ");
		sql.append("name='"+Util.changeToObj(productSchedule.getName())+"',");
		sql.append("time='"+Util.changeToObj(productSchedule.getTime())+"' ");
		sql.append("where id='"+productSchedule.getId()+"'");
		DBUtil.update(sql.toString());
	}

	@Override
	public ProductSchedule getProductScheduleByTime(String time)
			throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select  id,name,");
		sql.append("date_format(create_time,'%Y-%m-%d') as create_time,");
		sql.append("date_format(time,'%Y-%m-%d') as time ");
		sql.append(" from product_schedule ");
		sql.append(" where 1=1 ");
		sql.append(" and date_format(time,'%Y-%m-%d')=date_format('"+time+"','%Y-%m-%d')");
		List<ProductSchedule> result=DBUtil.query(sql.toString(),new BeanListHandler<ProductSchedule>(ProductSchedule.class));
		if(result.size()>0){
			return result.get(0);
		}
		return null;
	}

}
